- frmBusCardHolder_StudentRecord.vb
- project /
1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Public Class frmBusCardHolder_StudentRecord
4 Sub fillSession()
5 Try
6 con = New SqlConnection(cs)
7 con.Open()
8 adp = New sqlDataAdapter()
9 adp.SelectCommand = New SqlCommand("SELECT distinct (session) FROM Student,BusCardHolder_Student where Student.AdmissionNo=BusCardHolder_Student.AdmissionNo", con)
10 ds = New DataSet("ds")
11 adp.Fill(ds)
12 dtable = ds.Tables(0)
13 cmbSession.Items.Clear()
14 For Each drow As DataRow In dtable.Rows
15 cmbSession.Items.Add(drow(0).ToString())
16 Next
17 Catch ex As Exception
18 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
19 End Try
20 End Sub
21 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
22 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
23 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
24 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
25 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
26 End If
27 Dim b As Brush = SystemBrushes.ControlText
28 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
29
30 End Sub
31
32 Private Sub dgw_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
33 Try
34 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
35 If lblSet.Text = "Bus Holder Entry" Then
36 Me.Hide()
37 frmBusCardHolder_Student.Show()
38 frmBusCardHolder_Student.txtID.Text = dr.Cells(0).Value.ToString()
39 frmBusCardHolder_Student.txtAdmissionNo.Text = dr.Cells(1).Value.ToString()
40 frmBusCardHolder_Student.txtStudentName.Text = dr.Cells(2).Value.ToString()
41 frmBusCardHolder_Student.txtClass.Text = dr.Cells(3).Value.ToString()
42 frmBusCardHolder_Student.txtSection.Text = dr.Cells(4).Value.ToString()
43 frmBusCardHolder_Student.txtSchoolName.Text = dr.Cells(5).Value.ToString()
44 frmBusCardHolder_Student.cmbBusNo.Text = dr.Cells(6).Value.ToString()
45 frmBusCardHolder_Student.cmbLocationName.Text = dr.Cells(7).Value.ToString()
46 frmBusCardHolder_Student.dtpJoiningDate.Text = dr.Cells(8).Value.ToString()
47 frmBusCardHolder_Student.cmbStatus.Text = dr.Cells(9).Value.ToString()
48 frmBusCardHolder_Student.btnDelete.Enabled = True
49 frmBusCardHolder_Student.btnUpdate.Enabled = True
50 frmBusCardHolder_Student.btnSave.Enabled = False
51 End If
52 If lblSet.Text = "Bus Fee Payment" Then
53 Me.Hide()
54 frmBusFeePayment_Student.Show()
55 frmBusFeePayment_Student.txtBusHolderID.Text = dr.Cells(0).Value.ToString()
56 frmBusFeePayment_Student.txtAdmissionNo.Text = dr.Cells(1).Value.ToString()
57 frmBusFeePayment_Student.txtStudentName.Text = dr.Cells(2).Value.ToString()
58 frmBusFeePayment_Student.txtClass.Text = dr.Cells(3).Value.ToString()
59 frmBusFeePayment_Student.txtSection.Text = dr.Cells(4).Value.ToString()
60 frmBusFeePayment_Student.txtSchoolName.Text = dr.Cells(5).Value.ToString()
61 frmBusFeePayment_Student.txtLocation.Text = dr.Cells(7).Value.ToString()
62 frmBusFeePayment_Student.FillData()
63 frmBusFeePayment_Student.fillInstallment()
64 End If
65 Catch ex As Exception
66 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
67 End Try
68 End Sub
69
70 Public Sub GetData()
71 Try
72 con = New SqlConnection(cs)
73 con.Open()
74 cmd = New SqlCommand("SELECT RTRIM(BusCardHolder_Student.BCH_ID) as [ID],RTRIM(Student.AdmissionNo) as [Admission No.], RTRIM(StudentName) as [StudentName], RTRIM(ClassName) as [Class], RTRIM(SectionName) as Section,RTRIM(SchoolName) as [School Name],RTRIM(BusInfo.BusNo) as [Bus No.],RTRIM(LocationName) as [Location Name], CONVERT(DateTime,JoiningDate,105) as [Joining Date],RTRIM(BusCardHolder_Student.Status) as [Status] from Student,BusCardHolder_Student,Location,Section,Class,SchoolInfo,BusInfo where Student.SectionID=Section.ID and Location.LocationName=BusCardHolder_Student.Location and Student.AdmissionNo=BusCardHolder_Student.AdmissionNo and Class.ClassName=Section.Class and Student.SchoolID=SchoolInfo.S_ID and BusInfo.BusNo=BusCardHolder_Student.BusNo order by StudentName", con)
75 adp = New SqlDataAdapter(cmd)
76 ds = New DataSet()
77 adp.Fill(ds, "Student")
78 dgw.DataSource = ds.Tables("Student").DefaultView
79 con.Close()
80 Catch ex As Exception
81 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
82 End Try
83 End Sub
84
85 Private Sub txtStudentName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStudentName.TextChanged
86 Try
87 con = New SqlConnection(cs)
88 con.Open()
89 cmd = New SqlCommand("SELECT RTRIM(BusCardHolder_Student.BCH_ID) as [ID],RTRIM(Student.AdmissionNo) as [Admission No.], RTRIM(StudentName) as [StudentName], RTRIM(ClassName) as [Class], RTRIM(SectionName) as Section,RTRIM(SchoolName) as [School Name],RTRIM(BusInfo.BusNo) as [Bus No.],RTRIM(LocationName) as [Location Name], CONVERT(DateTime,JoiningDate,105) as [Joining Date],RTRIM(BusCardHolder_Student.Status) as [Status] from Student,BusCardHolder_Student,Location,Section,Class,SchoolInfo,BusInfo where Student.SectionID=Section.ID and Location.LocationName=BusCardHolder_Student.Location and Student.AdmissionNo=BusCardHolder_Student.AdmissionNo and Class.ClassName=Section.Class and Student.SchoolID=SchoolInfo.S_ID and BusInfo.BusNo=BusCardHolder_Student.BusNo and StudentName like '" & txtStudentName.Text & "%' order by StudentName", con)
90 adp = New SqlDataAdapter(cmd)
91 ds = New DataSet()
92 adp.Fill(ds, "Student")
93 dgw.DataSource = ds.Tables("Student").DefaultView
94 con.Close()
95 Catch ex As Exception
96 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
97 End Try
98 End Sub
99
100 Private Sub txtLocation_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtLocation.TextChanged
101 Try
102 con = New SqlConnection(cs)
103 con.Open()
104 cmd = New SqlCommand("SELECT RTRIM(BusCardHolder_Student.BCH_ID) as [ID],RTRIM(Student.AdmissionNo) as [Admission No.], RTRIM(StudentName) as [StudentName], RTRIM(ClassName) as [Class], RTRIM(SectionName) as Section,RTRIM(SchoolName) as [School Name],RTRIM(BusInfo.BusNo) as [Bus No.],RTRIM(LocationName) as [Location Name], CONVERT(DateTime,JoiningDate,105) as [Joining Date],RTRIM(BusCardHolder_Student.Status) as [Status] from Student,BusCardHolder_Student,Location,Section,Class,SchoolInfo,BusInfo where Student.SectionID=Section.ID and Location.LocationName=BusCardHolder_Student.Location and Student.AdmissionNo=BusCardHolder_Student.AdmissionNo and Class.ClassName=Section.Class and Student.SchoolID=SchoolInfo.S_ID and BusInfo.BusNo=BusCardHolder_Student.BusNo and LocationName like '" & txtLocation.Text & "%' order by StudentName", con)
105 adp = New SqlDataAdapter(cmd)
106 ds = New DataSet()
107 adp.Fill(ds, "Student")
108 dgw.DataSource = ds.Tables("Student").DefaultView
109 con.Close()
110 Catch ex As Exception
111 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
112 End Try
113 End Sub
114
115 Private Sub cmbSession_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSession.SelectedIndexChanged
116 Try
117 cmbClass.Enabled = True
118 con = New SqlConnection(cs)
119 con.Open()
120 Dim ct As String = "SELECT distinct RTRIM(ClassName) FROM Student,Section,Class where Student.SectionID=Section.ID and Section.Class=Class.Classname and session=@d1"
121 cmd = New SqlCommand(ct)
122 cmd.Connection = con
123 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
124 rdr = cmd.ExecuteReader()
125 cmbClass.Items.Clear()
126 While rdr.Read
127 cmbClass.Items.Add(rdr(0))
128 End While
129 con.Close()
130 Catch ex As Exception
131 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
132 End Try
133 End Sub
134
135 Private Sub cmbClass_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbClass.SelectedIndexChanged
136 Try
137 cmbSection.Enabled = True
138 con = New SqlConnection(cs)
139 con.Open()
140 Dim ct As String = "SELECT distinct RTRIM(SectionName) FROM Student,Section,Class where Student.SectionID=Section.ID and Section.Class=Class.ClassName and session=@d1 and ClassName=@d2"
141 cmd = New SqlCommand(ct)
142 cmd.Connection = con
143 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
144 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
145 rdr = cmd.ExecuteReader()
146 cmbSection.Items.Clear()
147 While rdr.Read
148 cmbSection.Items.Add(rdr(0))
149 End While
150 con.Close()
151 Catch ex As Exception
152 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
153 End Try
154 End Sub
155
156 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
157 Try
158 If Len(Trim(cmbSession.Text)) = 0 Then
159 MessageBox.Show("Please select session", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
160 cmbSession.Focus()
161 Exit Sub
162 End If
163 If Len(Trim(cmbClass.Text)) = 0 Then
164 MessageBox.Show("Please select class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
165 cmbClass.Focus()
166 Exit Sub
167 End If
168 If Len(Trim(cmbSection.Text)) = 0 Then
169 MessageBox.Show("Please select section", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
170 cmbSection.Focus()
171 Exit Sub
172 End If
173 con = New SqlConnection(cs)
174 con.Open()
175 cmd = New SqlCommand("SELECT RTRIM(BusCardHolder_Student.BCH_ID) as [ID],RTRIM(Student.AdmissionNo) as [Admission No.], RTRIM(StudentName) as [StudentName], RTRIM(ClassName) as [Class], RTRIM(SectionName) as Section,RTRIM(SchoolName) as [School Name],RTRIM(BusInfo.BusNo) as [Bus No.],RTRIM(LocationName) as [Location Name], CONVERT(DateTime,JoiningDate,105) as [Joining Date],RTRIM(BusCardHolder_Student.Status) as [Status] from Student,BusCardHolder_Student,Location,Section,Class,SchoolInfo,BusInfo where Student.SectionID=Section.ID and Location.LocationName=BusCardHolder_Student.Location and Student.AdmissionNo=BusCardHolder_Student.AdmissionNo and Class.ClassName=Section.Class and Student.SchoolID=SchoolInfo.S_ID and BusInfo.BusNo=BusCardHolder_Student.BusNo and Session=@d1 and Classname=@d2 and SectionName=@d3 order by StudentName", con)
176 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
177 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
178 cmd.Parameters.AddWithValue("@d3", cmbSection.Text)
179 adp = New SqlDataAdapter(cmd)
180 ds = New DataSet()
181 adp.Fill(ds, "Student")
182 dgw.DataSource = ds.Tables("Student").DefaultView
183 con.Close()
184 Catch ex As Exception
185 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
186 End Try
187 End Sub
188
189 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
190 Try
191 con = New SqlConnection(cs)
192 con.Open()
193 cmd = New SqlCommand("SELECT RTRIM(BusCardHolder_Student.BCH_ID) as [ID],RTRIM(Student.AdmissionNo) as [Admission No.], RTRIM(StudentName) as [StudentName], RTRIM(ClassName) as [Class], RTRIM(SectionName) as Section,RTRIM(SchoolName) as [School Name],RTRIM(BusInfo.BusNo) as [Bus No.],RTRIM(LocationName) as [Location Name], CONVERT(DateTime,JoiningDate,105) as [Joining Date],RTRIM(BusCardHolder_Student.Status) as [Status] from Student,BusCardHolder_Student,Location,Section,Class,SchoolInfo,BusInfo where Student.SectionID=Section.ID and Location.LocationName=BusCardHolder_Student.Location and Student.AdmissionNo=BusCardHolder_Student.AdmissionNo and Class.ClassName=Section.Class and Student.SchoolID=SchoolInfo.S_ID and BusInfo.BusNo=BusCardHolder_Student.BusNo and JoiningDate between @d1 and @d2 order by StudentName", con)
194 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "JoiningDate").Value = dtpDateFrom.Value.Date
195 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "JoiningDate").Value = dtpDateTo.Value.Date
196 adp = New SqlDataAdapter(cmd)
197 ds = New DataSet()
198 adp.Fill(ds, "Student")
199 dgw.DataSource = ds.Tables("Student").DefaultView
200 con.Close()
201 Catch ex As Exception
202 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
203 End Try
204 End Sub
205 Sub Reset()
206 txtLocation.Text = ""
207 txtStudentName.Text = ""
208 cmbClass.SelectedIndex = -1
209 cmbSection.SelectedIndex = -1
210 cmbSession.SelectedIndex = -1
211 cmbClass.Enabled = False
212 cmbSection.Enabled = False
213 GetData()
214 End Sub
215
216 Private Sub frmBusCardHolder_StudentRecord_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
217 GetData()
218 fillSession()
219 End Sub
220
221 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
222 Reset()
223 End Sub
224
225 Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
226 Me.Close()
227 End Sub
228
229 Private Sub btnExportExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnExportExcel.Click
230 Dim rowsTotal, colsTotal As Short
231 Dim I, j, iC As Short
232 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
233 Dim xlApp As New Excel.Application
234 Try
235 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
236 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
237 xlApp.Visible = True
238
239 rowsTotal = dgw.RowCount
240 colsTotal = dgw.Columns.Count - 1
241 With excelWorksheet
242 .Cells.Select()
243 .Cells.Delete()
244 For iC = 0 To colsTotal
245 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
246 Next
247 For I = 0 To rowsTotal - 1
248 For j = 0 To colsTotal
249 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
250 Next j
251 Next I
252 .Rows("1:1").Font.FontStyle = "Bold"
253 .Rows("1:1").Font.Size = 12
254
255 .Cells.Columns.AutoFit()
256 .Cells.Select()
257 .Cells.EntireColumn.AutoFit()
258 .Cells(1, 1).Select()
259 End With
260 Catch ex As Exception
261 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
262 Finally
263 'RELEASE ALLOACTED RESOURCES
264 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
265 xlApp = Nothing
266 End Try
267 End Sub
268 End Class
